# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql
import re
import logging
from dbutils.pooled_db import PooledDB

logger = logging.getLogger(__name__)
class LianjiaDetailsPipeline:
	def process_item(self, item, spider):
		return item


class MysqlExePipeline(object):
	def __init__(self, host, database, user, password, port, maxconnections, mincached, maxcached, blocking, maxshared):
		self.host = host
		self.database = database
		self.user = user
		self.password = password
		self.port = port
		self.creator = pymysql
		self.maxconnections = maxconnections
		self.mincached = mincached
		self.maxcached = maxcached
		self.blocking = blocking
		self.maxusage = None
		self.setsession = []
		self.ping = 0
		self.maxshared = maxshared

	@classmethod
	def from_crawler(cls, crawler):
		return cls(host=crawler.settings.get('MYSQL_HOME'),
				   password=crawler.settings.get('MYSQL_PASSWORD'),
				   database=crawler.settings.get('MYSQL_DATABASE'),
				   user=crawler.settings.get('MYSQL_USER'),
				   port=crawler.settings.get('MYSQL_PORT'),
				   maxconnections=crawler.settings.get("MYSQL_MAX_CONNECTIONS"),
				   mincached=crawler.settings.get("MYSQL_MIN_CACHED"),
				   maxcached=crawler.settings.get("MYSQL_MAX_CACHED"),
				   blocking=crawler.settings.get("MYSQL_BLOCKING"),
				   maxshared=crawler.settings.get("MYSQL_MAX_SHARED")
				   )

	def open_spider(self, spider):
		self.pool = PooledDB(creator=self.creator,
							 maxcached=self.maxcached,
							 mincached=self.mincached,
							 maxshared=self.maxshared,
							 maxusage=self.maxusage,
							 maxconnections=self.maxconnections,
							 blocking=self.blocking,
							 setsession=self.setsession,
							 ping=self.ping,
							 host=self.host,
							 port=self.port,
							 user=self.user,
							 password=self.password,
							 database=self.database,
							 )
		self.conn = self.pool.connection()  # 启动连接
		self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)

	def close_spider(self, spider):
		self.cursor.close()
		self.conn.close()
		self.pool.close()

	def process_item(self, item, spider):
		logger.info(f"pipelines 正在插入")
		data = dict(item)
		district = data.pop("district")
		city = data.pop("city")
		position = data.pop("position")
		data.pop("agencyFee")
		data.pop("facilityList")
		data.pop("paymentMethod")

		sel = """select citycode from city where province like '%s' and town like '%s' and district like '%s'""" % (
			"%" + position + "%", "%" + city + "%", "%" + district + "%")
		try:
			self.cursor.execute(sel)
			data["citycode"] = self.cursor.fetchone().get("citycode")
			self.conn.commit()
		except:
			print("地区不存在")
			# logger.error(f"citycode不存在:{e}")
			cityCode = self.cityCode(province=position, town=city, district=district)
			# print("cityCode",cityCode)
			print("城市", position, city, district)
			new_city_sql = "insert into city(citycode,province,town,district) values ('%(citycode)s','%(position)s','%(town)s','%(district)s')"
			new_city_sql % {"citycode": cityCode, "position": position, "town": city, "district": district}
			self.cursor.execute(new_city_sql % {"citycode": cityCode, "position": position, "town": city, "district": district})
			self.conn.commit()
			self.cursor.execute(sel)
			data["citycode"] = self.cursor.fetchone().get("citycode")
		# self.cursor.close()
		data["url"] = re.search(".*?zufang/(.*?)\..*?", data["url"]).group(1)
		data["title"] = data["title"][0]
		data["label"] = ','.join(data["label"])
		data["acreage"] = data["acreage"][0]
		data["rent"] = float(data["rent"])
		data["serviceCharge"] = float(data["serviceCharge"])
		data["deposit"] = float(data["deposit"])
		# data["facilityList"] = ','.join(data["facilityList"])
		data["basicInformation"] = ','.join(data["basicInformation"])
		data["source"] = "链家"
		# da = list(data.values())
		# print("data:", data)
		sql = """
		insert into details(id,title,squares,description,basicinto,rent,deposit,serviceMethod,label,citycode,source) 
		values('%(url)s','%(title)s','%(acreage)s','%(description)s','%(basicInformation)s',%(rent)s,%(deposit)s,%(serviceCharge)s,'%(label)s','%(citycode)s','%(source)s')""" \
			  % data
		try:
			self.cursor.execute(sql)
			self.conn.commit()
			logger.info(f"MysqlExePipeline 数据提交成功")
		except pymysql.err.IntegrityError as primary:
			logger.error(f"MysqlExePipeline 错误:{primary}:->" + str(data.get("id")))
			up_sql = """
			update details set title='%(title)s',squares='%(squares)s',description='%(description)s',basicinto='%(basicInformation)s',rent='%(rent)s',deposit='%(deposit)s',serviceMethod='%(serviceCharge)s',label='%(label)s' where id='%(url)s'
			""" % data
			self.cursor.execute(up_sql)
			logger.info(f"数据更新成功:{str(data.get('url'))}")
			self.conn.commit()
		# except:
		# 	# 当更新报错时将会执行如下代码获取城市的地址信息，生成cityCode
		# 	cityCode = self.cityCode(province=position, town=city, district=district)
		# 	new_city_sql = "insert into city(citycode,province,town,district) values ('%(citycode)s','%(position)s','%(town)s','%(district)s')"
		# 	self.cursor.execute(
		# 		new_city_sql % {"citycode": cityCode, "position": position, "town": city, "district": district})
		# 	self.cursor.execute(up_sql)
		# 	self.conn.commit()

	def cityCode(self, province, town, district):
		sql1 = "select citycode from city where province like '%(province)s'"
		sql2 = "select citycode from city where town like '%(town)s'"
		sql3 = "select citycode from city where district like '%(district)s'"
		if self.cursor.execute(sql1 % {"province": province}) <= 0:
			# 省份不存在
			pr_ci = "%s%s%s"
			self.cursor.execute("select substring(citycode,1,2) as 'province' from city order by citycode desc limit 1")
			_pr = self.cursor.fetchone().get('province')
			print(f"_pr:{_pr}")
			new_pr = int(_pr) + 1
			if new_pr >= 10:
				# 不加0
				pr = pr_ci % (str(new_pr), "01", "01")
				logger.error(f"省份不存在 --> 正在生成 --> : 成功产生新的cityCode --> {pr}")
				# cr = "insert into "
				return pr
			else:
				pr = pr_ci % ("0" + str(new_pr), "01", "01")
				logger.error(f"省份不存在 --> 正在生成 --> : 成功产生新的cityCode --> {pr}")
				return pr
		elif self.cursor.execute(sql2 % {"town": town}) <= 0 and self.cursor.execute(
				sql1 % {"province": province}) >= 0:
			# print("到这里")
			# 省份存在，城市不存在
			tw_ci = "%s%s%s"
			tw_sql = 'select substring(citycode,1,4) as "town_code" from city where town like "%s" order by citycode ' \
					 'desc limit 1'
			self.cursor.execute(tw_sql % ("%" + town + "%"))
			tw_code = self.cursor.fetchone().get("town_code")
			# print("tw_code:", tw_code)
			pr = tw_code[0:2]
			_tw = int(tw_code[2:4]) + 1
			# print("tw:", _tw)
			if _tw >= 10:
				# 不加0
				tw = tw_ci % (pr, str(_tw), "01")
				logger.error(f"城市不存在 --> 正在生成 --> : 成功产生新的cityCode --> {tw}")
				return tw
			# print()
			else:
				tw = tw_ci % (pr, "0" + str(_tw), "01")
				logger.error(f"城市不存在 --> 正在生成 --> : 成功产生新的cityCode --> {tw}")
				return tw
		elif self.cursor.execute(sql3 % {"district": district}) <= 0 and self.cursor.execute(
				sql2 % {"town": town}) >= 0 and self.cursor.execute(sql1 % {"province": province}) >= 0:
			di_ci = "%s%s%s"
			di_sql = "select citycode from city  where province like '%s' and town like '%s' order by citycode desc limit 1;"
			self.cursor.execute(di_sql % ("%" + province + "%", "%" + town + "%"))
			di = self.cursor.fetchone().get("citycode")
			_pr = di[0:2]
			_tw = di[2:4]
			di = int(di[4:6]) + 1
			if di >= 10:
				di = di_ci % (_pr, _tw, str(di))
				logger.error(f"区不存在 --> 正在生成 --> : 成功产生新的cityCode --> {di}")
				return di
			else:
				di = di_ci % (_pr, _tw, "0" + str(di))
				logger.error(f"区不存在 --> 正在生成 --> : 成功产生新的cityCode --> {di}")
				return di
		else:
			logger.error("产生失败 --> : 传入参数都存在")
			return None
